﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for BaiThiDAL
/// </summary>
public class BaiThiDAL
{
    public static object IsNull(object checkValue, object replaceValue)
    {
        if (checkValue.Equals(System.DBNull.Value) || checkValue == null)
            return replaceValue;
        return checkValue;
    }

    public static int ThemDanhSachThi(List<BaiThiINFO> dsBaiThi)
    {
        int mabaithi = GetMa();
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            conn = new SqlConnection(Conn.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            trans = conn.BeginTransaction();
            foreach (var item in dsBaiThi)
            {
                mabaithi++;
                item.MaBaiThi = mabaithi;
                if (Insert(item,trans) <= 0)
                {
                    trans.Rollback();
                    conn.Close();
                    return 0;
                }
            }
            trans.Commit();
            conn.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conn.Close();
            return 0;
        }
    }

    public static int HoanTatBaiThi(BaiThiINFO mBaiThi, List<ChiTietBaiThiINFO> dsChiTiet)
    {
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            int machitiet = ChiTietBaiThiDAL.getMaChiTiet(mBaiThi.MaBaiThi);
            conn = new SqlConnection(Conn.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            trans = conn.BeginTransaction();
            if (Update(mBaiThi, trans) <= 0)
            {
                trans.Rollback();
                conn.Close();
                return 0;
            }
            foreach (var item in dsChiTiet)
            {
                if (item.MaChiTiet <= 0)
                {
                    machitiet++;
                    item.MaBaiThi = mBaiThi.MaBaiThi;
                    item.MaChiTiet = machitiet;
                    if (ChiTietBaiThiDAL.Insert(item, trans) <= 0)
                    {
                        trans.Rollback();
                        conn.Close();
                        return 0;
                    }
                }
                else {
                    if (ChiTietBaiThiDAL.Update(item, trans) <= 0)
                    {
                        trans.Rollback();
                        conn.Close();
                        return 0;
                    }
                }
            }
            trans.Commit();
            conn.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conn.Close();
            return 0;
        }
    }

    public static int LuuDapAn(BaiThiINFO mBaiThi, ChiTietBaiThiINFO mChiTiet)
    {
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            int MaCT = ChiTietBaiThiDAL.getMaChiTiet(mBaiThi.MaBaiThi);
            conn = new SqlConnection(Conn.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            trans = conn.BeginTransaction();
            if (Update(mBaiThi, trans) <= 0)
            {
                trans.Rollback();
                conn.Close();
                return 0;
            }

            if (mChiTiet.MaChiTiet == 0)
            {
                MaCT++;
                mChiTiet.MaChiTiet = MaCT;
                if (ChiTietBaiThiDAL.Insert(mChiTiet, trans) <= 0)
                {
                    trans.Rollback();
                    conn.Close();
                    return 0;
                }
            }
            else {
                if (ChiTietBaiThiDAL.Update(mChiTiet, trans) <= 0)
                {
                    trans.Rollback();
                    conn.Close();
                    return 0;
                }
            }
           
            trans.Commit();
            conn.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conn.Close();
            return 0;
        }
    }

    public static int Insert(BaiThiINFO obj)
    {
        string sql = "INSERT INTO BaiThi (MaBaiThi,TinhTrang,Diem,NgayThi,ThoiGianBatDau,ThoiGianKetThuc,ThoiGianThi,MaHV,MaDe)";
        sql += " VALUES (" + obj.MaBaiThi + "," + obj.TinhTrang + "," + obj.Diem + ",'" + obj.NgayThi + "','" + obj.NgayThi + "','" + obj.NgayThi + "'," + obj.ThoiGianThi + "," + obj.MaHV + "," + obj.MaDe + ")";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Insert(BaiThiINFO obj, SqlTransaction trans)
    {
        string sql = "INSERT INTO BaiThi (MaBaiThi,TinhTrang,Diem,NgayThi,ThoiGianBatDau,ThoiGianKetThuc,ThoiGianThi,MaHV,MaDe)";
        sql += " VALUES (" + obj.MaBaiThi + "," + obj.TinhTrang + "," + obj.Diem + ",'" + obj.NgayThi + "','" + obj.NgayThi + "','" + obj.NgayThi + "'," + obj.ThoiGianThi + "," + obj.MaHV + "," + obj.MaDe + ")";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int InsertDeThiLop(int MaDe, int Malop)
    {
        string sql = "INSERT INTO DETHI_LOP (MaDe,MaLop)";
        sql += " VALUES (" + MaDe + "," + Malop + ")";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int DeleteDeThiLop(int MaDe, int Malop)
    {
        string sql = "Delete from DETHI_LOP WHERE MaDe = " + MaDe + " AND MaLop =" + Malop;
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static List<DeThiINFO> ListDeThiLop(int Malop)
    {
        string sql = "Select DeThi.* from DETHI_LOP INNER JOIN DETHI ON DETHI_LOP.MaDe = DETHI.MaDe WHERE DETHI_LOP.MaLop =" + Malop;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<DeThiINFO>(dr);
    }
    public static List<BaiThiINFO> ListBaiThiLop(int Malop, int MaDe)
    {
        string sql = "SELECT BAITHI.* ";
        sql += " FROM BAITHI INNER JOIN DETHI_LOP ON BaiThi.MaDe = DETHI_LOP.MaDe ";
        sql += " WHERE BAITHI.MaDe = " + MaDe;
        sql += " AND DETHI_LOP.MaLop = " + Malop;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<BaiThiINFO>(dr);
    }

    public static List<HocVienINFO> ListHocVienThi(int Malop, int MaDe)
    {
        string sql = "SELECT HocVien.* FROM";
        sql += " BAITHI INNER JOIN DETHI_LOP ON BaiThi.MaDe = DETHI_LOP.MaDe INNER JOIN HocVien ON BaiThi.MaHV = HocVien.MaHV";
        sql += " WHERE BAITHI.MaDe = " + MaDe;
        sql += " AND DETHI_LOP.MaLop = " + Malop;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetListObjects<HocVienINFO>(dr);
    }

    public static DataTable timkiem(int MaLop,int MaDe)
    {
        string sql = "SELECT BAITHI.*,HOCVIEN.TenHV, (SELECT TenDe FROM DETHI WHERE MaDe = BAITHI.MaDe) as TenDe";
        sql += " FROM BAITHI INNER JOIN  HOCVIEN ON BAITHI.MaHV = HOCVIEN.MaHV ";
        sql += " WHERE BAITHI.MAHV IN (SELECT MAHV FROM HOCVIEN_LOP WHERE MaLop = " + MaLop + ") ";
        sql += " AND BAITHI.MaDe = " + MaDe;
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }

    public static DataTable DanhSachDeThi(int MaHV)
    {
        string sql = "SELECT BAITHI.*, DETHI.TenDe FROM BAITHI INNER JOIN DETHI ON BAITHI.MaDe = DETHI.MaDe ";
        sql += " Where BAITHI.MaHV = " + MaHV + " AND BAITHI.TinhTrang IN (0,1) AND NgayThi <= getDate()";
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }

    public static DataTable DanhSachDeDaThi(int MaHV)
    {
        string sql = "SELECT BAITHI.*, DETHI.TenDe FROM BAITHI INNER JOIN DETHI ON BAITHI.MaDe = DETHI.MaDe ";
        sql += " Where BAITHI.MaHV = " + MaHV + " AND BAITHI.TinhTrang > 2";
        return SQLHelper.ExecuteDataTable(Conn.ConnectionString, sql, CommandType.Text);
    }

    public static int Update(BaiThiINFO obj)
    {
        string sql = "Update BaiThi set TinhTrang=N'" + obj.TinhTrang + "', Diem = " + obj.Diem + ", ThoiGianBatDau = '" + obj.ThoiGianBatDau + "',";
        sql += " ThoiGianKetThuc='" + obj.ThoiGianKetThuc + "', ThoiGianThi='" + obj.ThoiGianThi + "', MaHV=" + obj.MaHV + ", MaDe=" + obj.MaDe + " Where MaBaiThi=" + obj.MaBaiThi + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static int Update(BaiThiINFO obj, SqlTransaction trans)
    {
        string sql = "Update BaiThi set TinhTrang=N'" + obj.TinhTrang + "', Diem = " + obj.Diem + ", ThoiGianBatDau = '" + obj.ThoiGianBatDau + "',";
        sql += " ThoiGianKetThuc='" + obj.ThoiGianKetThuc + "', ThoiGianThi='" + obj.ThoiGianThi + "', MaHV=" + obj.MaHV + ", MaDe=" + obj.MaDe + " Where MaBaiThi=" + obj.MaBaiThi + "";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int UpdateTime(int MaBaiThi, DateTime TG)
    {
        string sql = "Update BaiThi set ThoiGianKetThuc='" + TG + "', ThoiGianThi = ThoiGianThi -3 Where MaBaiThi=" + MaBaiThi + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }

    public static BaiThiINFO getInfo(int MaBaiThi)
    {
        string sql = "Select * from BaiThi where MaBaiThi =" + MaBaiThi + "";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        return SQLHelper.SetObject<BaiThiINFO>(dr);
    }

    public static int Delete(int MaBaiThi)
    {
        string sql = "Delete from BaiThi where MaBaiThi =" + MaBaiThi + "";
        return SQLHelper.ExecuteNonQuery(Conn.ConnectionString, CommandType.Text, sql, null);
    }
    public static int Delete(int MaBaiThi, SqlTransaction trans)
    {
        string sql = "Delete from BaiThi where MaBaiThi =" + MaBaiThi + "";
        return SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql, null);
    }

    public static int KiemTraDeThiHocVien(int MaHV, int MaDe)
    {
        string sql = "SELECT count(MaBaiThi) FROM BaiThi WHERE MaHV=" + MaHV + " AND MaDe=" + MaDe;
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int ma = 0;
        if (dr.Read())
        {
            ma = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return ma;
    }

    public static int GetMa()
    {
        string sql = "SELECT ISNULL(MAX(MaBaiThi),0) FROM BaiThi";
        SqlDataReader dr = SQLHelper.ExecuteReader(Conn.ConnectionString, CommandType.Text, sql, null);
        int ma = 0;
        if (dr.Read())
        {
            ma = int.Parse(dr[0].ToString());
        }
        dr.Close();
        return ma;
    }

    public static int KhoaBaiThi(List<BaiThiINFO> dsBaiThi)
    {
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            conn = new SqlConnection(Conn.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            trans = conn.BeginTransaction();
            foreach (var item in dsBaiThi)
            {
                if (Update(item, trans) <= 0)
                {
                    trans.Rollback();
                    conn.Close();
                    return 0;
                }
            }
            trans.Commit();
            conn.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conn.Close();
            return 0;
        }
    }

    public static int DeleteAll(List<BaiThiINFO> dsBaiThi)
    {
        SqlConnection conn = null;
        SqlTransaction trans = null;
        try
        {
            conn = new SqlConnection(Conn.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            trans = conn.BeginTransaction();
            foreach (var item in dsBaiThi)
            {
                if (Delete(item.MaBaiThi, trans) <= 0)
                {
                    trans.Rollback();
                    conn.Close();
                    return 0;
                }
            }

            trans.Commit();
            conn.Close();
            return 1;
        }
        catch (Exception)
        {
            trans.Rollback();
            conn.Close();
            return 0;
        }
    }
}